﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace DatabaseLib
{
    public class DatabaseRep
    {
        private readonly string _connectionString;

        public DatabaseRep(string connectionString)
        {
            _connectionString = connectionString;
        }
        
        public void ExecuteQuery(string queryString, object param)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@param", param);
                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.ReadLine();
                }
            }
        }

        public void AddStud(Student stud)
        {
            string name = stud.Name;
            int id = stud.Id;
            string queryString ="INSERT INTO dbo.Students (Name) VALUES (@param)";
            ExecuteQuery(queryString, name);
        }

        public void RemoveStud(int id)
        {
            int identify = id;
            string queryString = "DELETE dbo.Students WHERE id=@param";
            ExecuteQuery(queryString, id);
        }

        public void Update(Student stud)
        {
            string name= stud.Name;
            int id=stud.Id;
            string queryString = "UPDATE dbo.Students SET Name=@param WHERE id="+id;
            ExecuteQuery(queryString, name);
        }

        public List<Student> GetAll()
        {
            string queryString = "SELECT id, name from dbo.Students ORDER BY id";
            List<Student> students = new List<Student>();

            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                try
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            students.Add(new Student((int)reader[0], (string)reader[1]));
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.ReadLine();
                }
            }
            return students;
        }

        public Student GetOne(int id)
        {
            string queryString = "SELECT id, name from dbo.Students WHERE id = @id;";
            int param = id;
            int identity=0;
            string name="";

            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@id", param);
                try
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            identity = (int) reader[0];
                            name = (string) reader[1];
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.ReadLine();
                }
            }
            return new Student(identity, name);
        }
    }
    
    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        
        public Student(int id, string name)
        {
            Id = id;
            Name = name;
        }
    }

}
